﻿namespace Hidistro.SqlDal.Members
{
    using Hidistro.Core;
    using Hidistro.Core.Entities;
    using Hidistro.Membership.Context;
    using Microsoft.Practices.EnterpriseLibrary.Data;
    using System;
    using System.Data;
    using System.Data.Common;
    using System.Text;

    public class FavoriteDao
    {
        private Database database = DatabaseFactory.CreateDatabase();

        public bool AddProductToFavorite(int productId, int userId)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("INSERT INTO Hishop_Favorite(ProductId, UserId, Tags, Remark)VALUES(@ProductId, @UserId, '', '')");
            this.database.AddInParameter(sqlStringCommand, "UserId", DbType.Int32, userId);
            this.database.AddInParameter(sqlStringCommand, "ProductId", DbType.Int32, productId);
            return (this.database.ExecuteNonQuery(sqlStringCommand) > 0);
        }

        private string BuildFavoriteQuery(int userId, string tags)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(" SELECT FavoriteId FROM Hishop_Favorite WHERE UserId = {0} ", userId);
            builder.AppendFormat(" AND ProductId IN (SELECT ProductId FROM Hishop_Products WHERE SaleStatus=1) ", new object[0]);
            if (!string.IsNullOrEmpty(tags))
            {
                builder.AppendFormat(" AND (ProductId IN (SELECT ProductId FROM Hishop_Products WHERE SaleStatus=1 AND ProductName LIKE '%{0}%') ", DataHelper.CleanSearchString(tags));
                builder.AppendFormat(" OR Tags LIKE '%{0}%')", DataHelper.CleanSearchString(tags));
            }
            builder.AppendFormat(" ORDER BY FavoriteId DESC", new object[0]);
            return builder.ToString();
        }

        public bool CheckHasCollect(int memberId, int productId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SELECT COUNT(1)");
            builder.AppendFormat(" FROM Hishop_Favorite WHERE UserId={0} AND ProductId ={1} ", memberId, productId);
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand(builder.ToString());
            int num2 = (int)this.database.ExecuteScalar(sqlStringCommand);
            return (num2 > 0);
        }

        public int DeleteFavorite(int favoriteId)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("DELETE FROM Hishop_Favorite WHERE FavoriteId = @FavoriteId");
            this.database.AddInParameter(sqlStringCommand, "FavoriteId", DbType.Int32, favoriteId);
            return this.database.ExecuteNonQuery(sqlStringCommand);
        }

        public bool DeleteFavorites(string ids)
        {
            string query = "DELETE from Hishop_Favorite WHERE FavoriteId IN (" + ids + ")";
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand(query);
            return (this.database.ExecuteNonQuery(sqlStringCommand) > 0);
        }

        public bool ExistsProduct(int productId, int userId)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT COUNT(*) FROM Hishop_Favorite WHERE UserId=@UserId AND ProductId=@ProductId");
            this.database.AddInParameter(sqlStringCommand, "UserId", DbType.Int32, userId);
            this.database.AddInParameter(sqlStringCommand, "ProductId", DbType.Int32, productId);
            return (((int)this.database.ExecuteScalar(sqlStringCommand)) > 0);
        }

        public DataTable GetFavorites()
        {
            Member user = HiContext.Current.User as Member;
            if (user == null) return null;
            StringBuilder builder = new StringBuilder();
            builder.Append("SELECT a.*, b.ProductName, b.ThumbnailUrl60, b.ThumbnailUrl310,b.SkuId,b.Stock, b.MarketPrice,b.ShortDescription,MonthlySales,Review,CategoryUrl,");
            int discount = new MemberGradeDao().GetMemberGrade(user.GradeId).Discount;
            builder.AppendFormat(" (CASE WHEN (SELECT COUNT(*) FROM Hishop_SKUMemberPrice WHERE SkuId = b.SkuId AND GradeId = {0}) = 1", user.GradeId);
            builder.AppendFormat(" THEN (SELECT MemberSalePrice FROM Hishop_SKUMemberPrice WHERE SkuId = b.SkuId AND GradeId = {0}) ELSE SalePrice*{1}/100 END) AS SalePrice", user.GradeId, discount);
            builder.AppendFormat(" FROM Hishop_Favorite a left join vw_Hishop_BrowseProductList b on a.ProductId = b.ProductId WHERE a.UserId={0} and b.ProductId > 0 ORDER BY a.FavoriteId DESC", user.UserId);
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand(builder.ToString());
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                return DataHelper.ConverDataReaderToDataTable(reader);
            }
        }

        public DbQueryResult GetFavorites(int userId, string tags, Pagination page)
        {
            DbQueryResult result = new DbQueryResult();
            DbCommand storedProcCommand = this.database.GetStoredProcCommand("ac_Member_Favorites_Get");
            this.database.AddInParameter(storedProcCommand, "PageIndex", DbType.Int32, page.PageIndex);
            this.database.AddInParameter(storedProcCommand, "PageSize", DbType.Int32, page.PageSize);
            this.database.AddInParameter(storedProcCommand, "IsCount", DbType.Boolean, page.IsCount);
            Member user = HiContext.Current.User as Member;
            this.database.AddInParameter(storedProcCommand, "GradeId", DbType.Int32, user.GradeId);
            this.database.AddInParameter(storedProcCommand, "SqlPopulate", DbType.String, this.BuildFavoriteQuery(userId, tags));
            this.database.AddOutParameter(storedProcCommand, "TotalFavorites", DbType.Int32, 4);
            using (IDataReader reader = this.database.ExecuteReader(storedProcCommand))
            {
                result.Data = DataHelper.ConverDataReaderToDataTable(reader);
                if (page.IsCount && reader.NextResult())
                {
                    reader.Read();
                    result.TotalRecords = reader.GetInt32(0);
                }
            }
            return result;
        }

        public int UpdateFavorite(int favoriteId, string tags, string remark)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("UPDATE Hishop_Favorite SET Tags = @Tags, Remark = @Remark WHERE FavoriteId = @FavoriteId");
            this.database.AddInParameter(sqlStringCommand, "Tags", DbType.String, tags);
            this.database.AddInParameter(sqlStringCommand, "Remark", DbType.String, remark);
            this.database.AddInParameter(sqlStringCommand, "FavoriteId", DbType.Int32, favoriteId);
            return this.database.ExecuteNonQuery(sqlStringCommand);
        }
    }
}

